const connection = require('../app/database');
const { APP_HOST, APP_PORT } = require('../app/config');

class MomentService {

  // 创建动态
  async create(user, content) {
    // 插入数据库
    try {
      const statement = `INSERT INTO moment (content, user_id) values(?, ?)`;
      const result = await connection.execute(statement, [content, user.id]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 根据id获取动态
  async getMomentById(momentId) {
    try {
      const statement = `
      SELECT 
        m.id id,
        m.content content, 
        m.likes likes,
        m.createAt createTime,
        m.updateAt updateTime,
        JSON_OBJECT('id', u.id, 'nickname', u.nickname, 'avatarUrl', u.avatar_url, 'isPrivate', u.private, 'createTime', u.createAt) author,
      IF(
        COUNT(l.id),
        JSON_ARRAYAGG(
          JSON_OBJECT('id', l.id, 'name', l.name)), NULL) labels,
          (
            SELECT
            IF(
              COUNT(c.id),
              JSON_ARRAYAGG(
                JSON_OBJECT(
                  'id', c.id, 
                  'content', c.content,
                  'likes', c.likes,
                  'commentId', c.comment_id, 
                  'createTime', c.createAt, 
                  'updateTime', c.updateAt,
                  'user', JSON_OBJECT('id', cu.id, 'nickname', cu.nickname,'avatarUrl', cu.avatar_url, 'isPrivate', cu.private, 'createTime', cu.createAt)
                )
              ),NULL)
          FROM 
          comment c 
          LEFT JOIN user cu 
          ON c.user_id = cu.id 
          WHERE m.id = c.moment_id
        ) comments,
        (SELECT JSON_ARRAYAGG(CONCAT('${APP_HOST}:','${APP_PORT}','/moment/images/',file.filename)) FROM file WHERE m.id = file.moment_id) images
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LEFT JOIN moment_label ml ON m.id = ml.moment_id
      LEFT JOIN label l ON ml.label_id = l.id
      WHERE m.id = ?`;
      const [result] = await connection.execute(statement, [momentId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 获取动态列表
  async getMomentList(offset, limit) {
    try {
      const statement = `
      SELECT 
        m.id id,
        m.content content,
        JSON_OBJECT('id', u.id, 'nickname', u.nickname, 'avatarUrl', u.avatar_url, 'isPrivate', u.private) author,
        (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) momentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount,
        m.likes likes,
        JSON_ARRAYAGG(JSON_OBJECT('id', l.id, 'name', l.name)) labels,
        (SELECT JSON_ARRAYAGG(CONCAT('${APP_HOST}:','${APP_PORT}','/moment/images/',file.filename)) FROM file WHERE m.id = file.moment_id) images,
        m.createAt createTime,
        m.updateAt updateTime
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LEFT JOIN moment_label ml ON m.id = ml.moment_id
      LEFT JOIN label l ON ml.label_id = l.id
      WHERE NOT ISNULL(ml.label_id)
      LIMIT ?, ?`;
      const result = await connection.execute(statement, [offset, limit]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 修改动态
  async update(content, momentId) {
    try {
      const statement = `UPDATE moment SET content = ? WHERE id = ?`;
      const result = await connection.execute(statement, [content, momentId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 删除动态
  async remove(momentId) {
    try {
      const statement = `DELETE FROM moment WHERE id = ?`;
      const result = await connection.execute(statement, [momentId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

}

module.exports = new MomentService();
